Skip to main content

Pandas Tutorial: Core Concepts

pandas is the most widely used Python library for data manipulation and analysis. It provides fast, flexible data structures (DataFrame and Series) designed to work with structured (tabular) data and time series.

In this tutorial, we'll cover the core concepts of pandas, from installation through to advanced operations and a capstone project.

📥 Download the Sample Data​

All the datasets used in this tutorial are available for download. Save them to your working directory to follow along.

FileDescriptionLink
sales_data.csvSales transactions for the capstone project (~500 rows)Download
employees.csvEmployee records for merge/join examples (20 rows)Download
departments.csvDepartment data for merge/join examples (5 rows)Download
weather_data.csvDaily weather time series (2 years)Download
student_scores.csvStudent scores for reshape/pivot examples (30 rows)Download

💡 Tip: Place all files in the same directory as your notebook or script, then use the filenames directly in pd.read_csv().

1. Introduction to pandas​

Key Features​

  • DataFrame — a 2D labeled data structure (like a spreadsheet or SQL table)
  • Series — a 1D labeled array (like a single column)
  • Vectorized operations — apply operations to entire arrays without explicit loops
  • Built-in I/O — read/write CSV, Excel, JSON, SQL, Parquet, and more
  • Time series support — date parsing, resampling, rolling windows
  • Integration — works seamlessly with NumPy, Matplotlib, scikit-learn

Installation​

pip install pandas

Import Convention​

import pandas as pd
import numpy as np

2. Core Data Structures​

Series — 1D Labeled Array​

# From a list
s = pd.Series([10, 20, 30, 40])

# With a custom index
s = pd.Series([10, 20, 30], index=["a", "b", "c"])

# From a dictionary (keys become the index)
s = pd.Series({"x": 100, "y": 200, "z": 300})

DataFrame — 2D Table​

# From a dictionary of lists
df = pd.DataFrame({
    "name": ["Alice", "Bob", "Carol"],
    "age":  [25, 30, 35],
    "city": ["Lagos", "Accra", "Nairobi"],
})

# From a list of dicts
df = pd.DataFrame([
    {"name": "Alice", "age": 25},
    {"name": "Bob",   "age": 30},
])

Inspecting a DataFrame​

df.shape        # (rows, columns)
df.dtypes       # data type of each column
df.columns      # column labels
df.info()       # concise summary: dtypes, non-null counts, memory use
df.describe()   # statistics for numeric columns
df.head()       # first 5 rows
df.tail()       # last 5 rows
df.sample(5)    # 5 random rows

3. Indexing and Selection​

.loc — Label-Based​

df.loc["a"]                    # single row by label
df.loc[["a", "c"]]             # multiple rows
df.loc["a":"c"]                # slice (inclusive)
df.loc["b", "age"]             # single cell
df.loc[:, "age"]               # all rows, one column

.iloc — Position-Based​

df.iloc[0]             # first row
df.iloc[-1]            # last row
df.iloc[0:2]           # first two rows (end-exclusive)
df.iloc[0, 1]          # cell at row 0, column 1
df.iloc[:, 2]          # all rows, column at position 2

Boolean Filtering​

df[df["age"] > 30]
df[(df["age"] > 25) & (df["city"] == "Accra")]
df[df["name"].str.startswith("A")]
df[df["city"].str.contains("ro")]

.query() — Readable Filtering​

df.query("age > 30")
df.query("age > 25 and city == 'Accra'")
min_age = 30
df.query("age >= @min_age")

Setting the Index​

df.set_index("name", inplace=True)
df.reset_index(inplace=True)

Fast Scalar Access​

df.at["b", "age"]     # label-based scalar
df.iat[1, 1]          # position-based scalar

4. Data Cleaning​

Missing Values​

df.isnull().sum()                    # count NaN per column
df.dropna()                          # drop rows with any NaN
df.dropna(subset=["age", "salary"])  # drop rows where specific columns are NaN
df.fillna(0)                         # replace all NaN with 0
df["age"].fillna(df["age"].mean())   # fill with column mean
df.fillna(method="ffill")            # forward fill
df["price"].interpolate()            # linear interpolation

Duplicates​

df.duplicated().sum()
df.drop_duplicates()
df.drop_duplicates(subset=["email"])
df.drop_duplicates(keep="last")

Data Types​

df["age"] = df["age"].astype(int)
df["date"] = pd.to_datetime(df["date"])
df["revenue"] = pd.to_numeric(df["revenue"], errors="coerce")
df["country"] = df["country"].astype("category")

String Cleaning​

df["name"] = df["name"].str.strip().str.lower()
df["phone"] = df["phone"].str.replace("-", "", regex=False)
df[["first", "last"]] = df["name"].str.split(" ", expand=True)

5. Transforming Data​

Column Arithmetic​

df["profit"] = df["revenue"] - df["cost"]
df["margin_pct"] = (df["profit"] / df["revenue"]) * 100

apply, map, assign​

df["age_group"] = df["age"].apply(lambda x: "Young" if x < 30 else "Senior")
df["country_code"] = df["country"].map({"Nigeria": "NG", "Ghana": "GH"})

df = df.assign(
    profit=lambda x: x["revenue"] - x["cost"],
    year=lambda x: x["date"].dt.year,
)

Binning​

df["age_bin"] = pd.cut(df["age"], bins=[0, 18, 35, 60, 100],
                        labels=["Child", "Young Adult", "Adult", "Senior"])
df["score_quartile"] = pd.qcut(df["score"], q=4, labels=["Q1", "Q2", "Q3", "Q4"])

Conditional Logic​

import numpy as np
df["flag"] = np.where(df["score"] >= 80, "pass", "fail")

conditions = [df["score"] >= 90, df["score"] >= 75, df["score"] >= 60]
choices    = ["A", "B", "C"]
df["grade"] = np.select(conditions, choices, default="F")

6. Aggregation and Grouping​

Basic GroupBy​

df.groupby("dept")["salary"].mean()
df.groupby(["dept", "years"])["salary"].sum()

Multiple Aggregations​

df.groupby("dept")["salary"].agg(["min", "max", "mean", "count"])

df.groupby("dept").agg(
    avg_salary=("salary", "mean"),
    headcount=("name", "count"),
)

Transform​

df["dept_avg_salary"] = df.groupby("dept")["salary"].transform("mean")

Filter Groups​

df.groupby("dept").filter(lambda g: len(g) > 1)

7. Merging and Joining​

pd.concat — Stacking​

combined = pd.concat([df1, df2], ignore_index=True)
pd.concat([df_a, df_b], axis=1)  # horizontal stacking

pd.merge — SQL-style Joins​

pd.merge(employees, departments, on="dept_id")           # inner (default)
pd.merge(employees, departments, on="dept_id", how="left")
pd.merge(employees, departments, on="dept_id", how="right")
pd.merge(employees, departments, on="dept_id", how="outer")
pd.merge(orders, customers, left_on="customer_id", right_on="id")

DataFrame.join — Index-based​

df1.join(df2)  # left join on index

8. Reshaping Data​

Pivot (Long → Wide)​

wide = df.pivot(index="date", columns="product", values="sales")
df.pivot_table(index="date", columns="product", values="sales", aggfunc="sum")

Melt (Wide → Long)​

long = wide.melt(id_vars="name", var_name="subject", value_name="score")

Stack / Unstack​

stacked = df.stack()
stacked.unstack()

Explode​

df.explode("tags")  # one row per list element

9. Time Series​

Parsing Dates​

df["date"] = pd.to_datetime(df["date"])
df["date"] = pd.to_datetime(df["date"], format="%d/%m/%Y")

DatetimeIndex​

df.set_index("date", inplace=True)
df["2024"]           # all rows in 2024
df["2024-03"]        # all rows in March 2024

.dt Accessor​

df["year"]  = df["date"].dt.year
df["month"] = df["date"].dt.month
df["dow"]   = df["date"].dt.day_name()

Resampling​

df.resample("M")["revenue"].sum()    # monthly sum
df.resample("W")["price"].mean()     # weekly mean
df.resample("D").ffill()             # upsample & forward-fill

Rolling Windows​

df["7d_avg"]  = df["revenue"].rolling(window=7).mean()
df["30d_sum"] = df["revenue"].rolling(window=30).sum()

Shifting​

df["prev_day"] = df["revenue"].shift(1)
df["change"]   = df["revenue"].diff(1)
df["pct_chg"]  = df["revenue"].pct_change(1)

10. Input / Output​

CSV​

df = pd.read_csv("data.csv", parse_dates=["date"], usecols=["name", "age"])
df.to_csv("output.csv", index=False)

Excel​

df = pd.read_excel("data.xlsx", sheet_name="Sales")
df.to_excel("output.xlsx", index=False)

JSON​

df = pd.read_json("data.json")
df.to_json("output.json", orient="records", indent=2)

SQL​

from sqlalchemy import create_engine
engine = create_engine("postgresql://user:pass@localhost/mydb")
df = pd.read_sql("SELECT * FROM orders", engine)
df.to_sql("orders_clean", engine, if_exists="replace", index=False)

Parquet​

df.to_parquet("data.parquet", index=False)
df = pd.read_parquet("data.parquet")

11. Performance Best Practices​

Choose the Right dtypes​

df["age"] = pd.to_numeric(df["age"], downcast="integer")
df["country"] = df["country"].astype("category")

Avoid Loops — Use Vectorized Operations​

# Bad
for i, row in df.iterrows():
    df.at[i, "tax"] = row["salary"] * 0.15

# Good
df["tax"] = df["salary"] * 0.15

Method Chaining​

result = (
    pd.read_csv("data.csv")
    .dropna(subset=["revenue"])
    .assign(year=lambda x: x["date"].dt.year)
    .query("year >= 2023")
    .groupby("region")["revenue"]
    .sum()
    .reset_index()
)

Read Only What You Need​

pd.read_csv("data.csv", usecols=["name", "salary"], nrows=10_000)

12. Visualization​

Built-in Plotting​

df["revenue"].plot(title="Revenue", figsize=(10, 4))
df.groupby("region")["revenue"].sum().plot(kind="bar")
df["age"].plot(kind="hist", bins=20, edgecolor="black")
df.plot(kind="scatter", x="age", y="salary")

Seaborn​

import seaborn as sns
sns.histplot(data=df, x="age", kde=True)
sns.boxplot(data=df, x="dept", y="salary")
sns.heatmap(df.corr(numeric_only=True), annot=True)

Saving Figures​

plt.savefig("chart.png", dpi=150, bbox_inches="tight")

13. Capstone Project​

This end-to-end project applies everything you've learned.

Load and Inspect​

df = pd.read_csv("sales_data.csv")
print(df.shape, df.dtypes, df.head())

Clean​

df["date"] = pd.to_datetime(df["date"], errors="coerce")
df = df.dropna(subset=["order_id", "date", "quantity"])
df = df.drop_duplicates(subset=["order_id"])
df["region"] = df["region"].str.strip().str.title()
df["category"] = df["category"].astype("category")

Transform​

df["revenue"] = df["quantity"] * df["unit_price"] * (1 - df["discount"])
df["profit"] = df["revenue"] - df["cost"]
df["year"] = df["date"].dt.year
df["month"] = df["date"].dt.to_period("M")

Aggregate​

by_region = df.groupby("region").agg(
    total_revenue=("revenue", "sum"),
    total_profit=("profit", "sum"),
    order_count=("order_id", "count"),
).sort_values("total_revenue", ascending=False)

Visualize​

fig, axes = plt.subplots(2, 2, figsize=(14, 10))
by_region["total_revenue"].plot(kind="barh", ax=axes[0, 1], title="Revenue by Region")
df.groupby("month")["revenue"].sum().plot(ax=axes[0, 0], title="Monthly Revenue")
plt.tight_layout()
plt.savefig("dashboard.png", dpi=150)
plt.show()

Summary​

print(f"Total Revenue: ${df['revenue'].sum():,.0f}")
print(f"Total Profit:  ${df['profit'].sum():,.0f}")
print(f"Total Orders:  {df['order_id'].nunique():,}")

Summary​

pandas is the essential tool for data manipulation in Python. Here's a recap of the key concepts we covered:

  • pd.Series() / pd.DataFrame(): Core data structures
  • .loc / .iloc: Label and position-based selection
  • Boolean filtering / .query(): Conditional row selection
  • fillna() / dropna(): Handle missing values
  • groupby() / agg(): Group and summarise data
  • pd.merge() / pd.concat(): Combine DataFrames
  • pivot() / melt(): Reshape between wide and long formats
  • resample() / rolling(): Time series operations
  • pd.read_csv() / to_csv(): Input and output
  • .plot(): Quick visualization